package cn.edu.xaut.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.edu.xaut.bean.AwardsInfo;
import cn.edu.xaut.bean.PaperCiteInfo;
import cn.edu.xaut.bean.SubInfo;
import cn.edu.xaut.bean.UserInfo;
import cn.edu.xaut.bean.UserProject;
import cn.edu.xaut.util.DBOperation;

public class ManageDaoImpl implements ManageDao {

	@Override
	public List<PaperCiteInfo> showPaperCite() {
		List<PaperCiteInfo> list = null;
		String sql = "select paperSerialNumber,paperTitle,numberOfReferences from paper";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			list = new ArrayList<PaperCiteInfo>();
			conn = DBOperation.getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			
			while(rs.next()) {
				String paperSerialNumber = rs.getString("paperSerialNumber");
				String paperTitle = rs.getString("paperTitle");
				int numberOfReferences = rs.getInt("numberOfReferences");
				
				PaperCiteInfo citeinfo = new PaperCiteInfo();
				citeinfo.setNumberOfReferences(numberOfReferences);
				citeinfo.setPaperSerialNumber(paperSerialNumber);
				citeinfo.setPaperTitle(paperTitle);
				
				list.add(citeinfo);
			}
				
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DBOperation.close(conn);
		}
			return list;
	}

	@Override
	public List<UserProject> showProjects() {
		List<UserProject> list = null;
		String sql = "select * from project";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			list = new ArrayList<UserProject>();
			conn = DBOperation.getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
	
			while(rs.next()) {
				String projectSerialNumber = rs.getString("projectSerialNumber");
				String projectName = rs.getString("projectName");
				String projectCategory = rs.getString("projectCategory");
				String district = rs.getString("district");
				String projectLeader = rs.getString("projectLeader");
				String projectTime = rs.getString("projectTime");
				int projectExpenditure = rs.getInt("projectExpenditure");
				int projectStatus = rs.getInt("projectStatus");
				
				UserProject uproj = new UserProject();
				uproj.setProjectSerialNumber(projectSerialNumber);
				uproj.setProjectName(projectName);
				uproj.setProjectCategory(projectCategory);
				uproj.setDistrict(district);
				uproj.setProjectLeader(projectLeader);
				uproj.setProjectTime(projectTime);
				uproj.setProjectExpenditure(projectExpenditure);
				uproj.setProjectStatus(projectStatus);
				
				list.add(uproj);
			}
				
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DBOperation.close(conn);
		}
			return list;
	}

	@Override
	public int insertAward(AwardsInfo awd) {
		PreparedStatement ps = null;
		Connection conn = null;
		int count = 0;
		try {
			conn = DBOperation.getConnection();
			String sql = "insert award(levelOfAward,awardProjectSerialNumber,awardProjectName,awardTime) values (?,?,?,?)";
			ps = conn.prepareStatement(sql);
				
			ps.setInt(1,awd.getLevelOfAward());
			ps.setString(2,awd.getAwardProjectSerialNumber());
			ps.setString(3,awd.getAwardProjectName());
			ps.setString(4,awd.getAwardTime());
			count = ps.executeUpdate();
			if(count > 0) 
				System.out.println("insert successfully!");
			
		}catch(SQLException e){
				e.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}
		finally {
			try {
				if(ps!= null ) ps.close();
				if(conn != null) conn.close();
			}catch(SQLException e) {
				e.printStackTrace();
			}
		}
		return count;
	}

	@Override
	public List<AwardsInfo> showAwards() {
		List<AwardsInfo> list = null;
		String sql = "select * from award";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			list = new ArrayList<AwardsInfo>();
			conn = DBOperation.getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			
			while(rs.next()) {
				int levelOfAward = rs.getInt("levelOfAward");
				String awardProjectSerialNumber = rs.getString("awardProjectSerialNumber");
				String awardProjectName = rs.getString("awardProjectName");
				String awardTime = rs.getString("awardTime");
								
				AwardsInfo awd = new AwardsInfo();
				awd.setLevelOfAward(levelOfAward);
				awd.setAwardProjectSerialNumber(awardProjectSerialNumber);
				awd.setAwardProjectName(awardProjectName);
				awd.setAwardTime(awardTime);
				list.add(awd);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DBOperation.close(conn);
		}
		return list;
	}

	@Override
	public void modifyProjStatus(String projectSerialNumber,int projectStatus) {
		String sql = "update project set projectStatus=? where projectSerialNumber=?";
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DBOperation.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1,projectStatus);
			ps.setString(2, projectSerialNumber);
			ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}
		finally {
			try {
				if(ps!= null ) ps.close();
				if(conn != null) conn.close();
			}catch(SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public List<UserProject> showSearchs(String searchInfo,String searchCategory) {
		List<UserProject> list = null;
		String sql="select * from project where "+searchCategory+" = ?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			list = new ArrayList<UserProject>();
			conn = DBOperation.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1,searchInfo);
			rs = ps.executeQuery();
			while(rs.next()) {
				String projectSerialNumber = rs.getString("projectSerialNumber");
				String projectName = rs.getString("projectName");
				String projectCategory = rs.getString("projectCategory");
				String district = rs.getString("district");
				String projectLeader = rs.getString("projectLeader");
				String projectTime = rs.getString("projectTime");
				int projectExpenditure = rs.getInt("projectExpenditure");
				int projectStatus = rs.getInt("projectStatus");
				
				UserProject uproj = new UserProject();
				uproj.setProjectSerialNumber(projectSerialNumber);
				uproj.setProjectName(projectName);
				uproj.setProjectCategory(projectCategory);
				uproj.setDistrict(district);
				uproj.setProjectLeader(projectLeader);
				uproj.setProjectTime(projectTime);
				uproj.setProjectExpenditure(projectExpenditure);
				uproj.setProjectStatus(projectStatus);
				list.add(uproj);
			}
				
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DBOperation.close(conn);
		}
			return list;
	}

	public void modifySub(String projectSerialNumber, int projectStatus) {
		String sql = "update declaration set projectStatus=? where projectSerialNumber=?";
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DBOperation.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1,projectStatus);
			ps.setString(2,projectSerialNumber);
			ps.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DBOperation.close(conn);
		}
	}

	public List<SubInfo> getSubInfo() {
		List<SubInfo> list = null;
		String sql = "select * from declaration";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			list = new ArrayList<SubInfo>();
			conn = DBOperation.getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				String projectSerialNumber = rs.getString("projectSerialNumber");
				String projectName = rs.getString("projectName");
				String projectCategory = rs.getString("projectCategory");
				String district = rs.getString("district");
				String projectLeader = rs.getString("projectLeader");
				String projectTime = rs.getString("projectTime");
				int projectExpenditure = rs.getInt("projectExpenditure");
				int projectStatus = rs.getInt("projectStatus");
				
				SubInfo sub = new SubInfo();
				sub.setProjectSerialNumber(projectSerialNumber);
				sub.setProjectName(projectName);
				sub.setProjectCategory(projectCategory);
				sub.setDistrict(district);
				sub.setProjectLeader(projectLeader);
				sub.setProjectTime(projectTime);
				sub.setProjectExpenditure(projectExpenditure);
				sub.setProjectStatus(projectStatus);
				list.add(sub);
			}
				
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DBOperation.close(conn);
		}
			return list;
	}

	public List<UserInfo> getAllUsers() {
		List<UserInfo> list = null;
		String sql = "select * from users";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			list = new ArrayList<UserInfo>();
			conn = DBOperation.getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			
			while(rs.next()) {

				String username = rs.getString("username");
				String password = rs.getString("password");
								
				UserInfo info = new UserInfo();

				info.setUsername(username);
				info.setPassword(password);
				list.add(info);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DBOperation.close(conn);
		}
		return list;
	}

	public void delUser(String username) {
		String sql = "delete from users where username = ?";
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DBOperation.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			ps.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DBOperation.close(conn);
		}
	}
}
